﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Threading;
using System.Globalization;
using System.Threading.Tasks;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;
using MySql.Data.MySqlClient;
using Mpi.Common;
using Mpi.Common.Collections;
using Xeml.Document;
using Xeml.Sampling.Contracts;
using XemlUtilities.ISA;

namespace XemlUtilities.DataSet
{
    public partial class XemlDataSet
    {
        protected const int BulkCopyBatchSize = 1000;
        protected const int BulkCopyTimeout = 60;
        protected const string xemlSchemaName = "xeml";

        protected IDataProvider _dataProvider;

        public virtual string GetUserNameFunction() { return String.Empty; }
        public virtual void BulkCopyTable(DataTable dataTable, IDbConnection con, IDbTransaction trans, bool throwOnDuplicate = true) { }
        public virtual bool ExperimenterExists(String email, IDbConnection connection, IDbTransaction transaction) { throw new NotImplementedException("not to be vcalled from user code"); }
        protected virtual IDbCommand GetFillCommand(Guid XemlId, IDbConnection connection)
        {
            throw new NotImplementedException("not to be vcalled from user code");
        }

        public static XemlDataSet GetInstance(Guid XemlId, IDataProvider dataProvider)
        {
            XemlDataSet xds = null;
            switch (dataProvider.BuildConnection().GetType().FullName)
            {
                case "MySql.Data.MySqlClient.MySqlConnection":
                    xds = new MySQLXemlDataSet(dataProvider);
                    break;
                case "System.Data.SqlClient.SqlConnection":
                default:
                    xds = new MSSQLXemlDataSet(dataProvider);
                    break;
            }

            xds.Fill(XemlId);

            return xds;
        }

        public static XemlDataSet GetInstance(XemlDocument xemlDocument, IDataProvider dataProvider)
        {
            XemlDataSet xds = null;
            switch (dataProvider.BuildConnection().GetType().FullName)
            {
                case "System.Data.SqlClient.SqlConnection":
                    xds = new MSSQLXemlDataSet(xemlDocument, dataProvider);
                    break;
                case "MySql.Data.MySqlClient.MySqlConnection":
                    xds = new MySQLXemlDataSet(xemlDocument, dataProvider);
                    break;
                default: xds = new MSSQLXemlDataSet(xemlDocument, dataProvider); break;
            }

            return xds;
        }

        public XemlDataSet(XemlDocument xemlDocument, IDataProvider dataProvider)
            : this(dataProvider)
        {

            BeginLoadData();

            // update the xml code because this is later pushed into the document table as well
            xemlDocument.RefreshXeml();

            // add the experimenter explicitly
            Experimenter.Add(xemlDocument.ExperimentHeader.Experimenter.Email, xemlDocument.ExperimentHeader.Experimenter.FirstName, xemlDocument.ExperimentHeader.Experimenter.LastName, xemlDocument.ExperimentHeader.Experimenter.Organisation);

            short tmpDocumentId = Document.Add(xemlDocument);

            // now load the ontologyhandlers (uri's) from the DB
            OntologyHandler.LoadFromDataProvider(dataProvider);

            List<ReadOnlyCollection<OntologyHandlerResource>> documentResourceOntologies = new List<ReadOnlyCollection<OntologyHandlerResource>>() {
                xemlDocument.DocumentResources.EnvironmentOntologyHandler,
            	xemlDocument.DocumentResources.GermPlasmOntologyHandler,
            	xemlDocument.DocumentResources.OrganismStructureOntologyHandler,
            	xemlDocument.DocumentResources.DevelopmentalOntologyHandler,
            	xemlDocument.DocumentResources.PositioningOntologyHandler,
            };

            // add the ontologyhandlers that are not in the DB yet
            foreach (ReadOnlyCollection<OntologyHandlerResource> documentResourceOntology in documentResourceOntologies)
            {
                foreach (var item in documentResourceOntology)
                {
                    if (!OntologyHandler.Select(x => x.Uri).Contains(item.Uri))
                    {
                        OntologyHandler.Add(item.Uri);
                    }
                }
                Ontology.Add(tmpDocumentId, documentResourceOntology);
            }

            DataProvider.Add(tmpDocumentId, xemlDocument.DocumentResources.DataProvider);
            ExperimentKeywords.Add(tmpDocumentId, xemlDocument.ExperimentHeader.Keywords);
            Story.Add(tmpDocumentId, xemlDocument.StoryBoard.VirtualRoot.SubNodes(false));

            foreach (var providerName in xemlDocument.ProviderDataMappings.Keys)
            {
                foreach (int sampleId in xemlDocument.ProviderDataMappings[providerName].ListDistinctIdentifiers())
                {
                    StoryBase story = xemlDocument.GetStoryBySampleId(sampleId);
                    short storyId = Convert.ToInt16(story.StoryBoard.VirtualRoot.FindNode(story, true).Index);
                    SampleMapping.Add(tmpDocumentId, storyId, sampleId, providerName, xemlDocument.ProviderDataMappings);
                }
            }
            EndLoadData();

            //compute the replica groups
            List<int> HashCodes = new List<int>();
            Dictionary<Description, List<int>> GroupDescriptions = new Dictionary<Description, List<int>>();
            foreach (Description d in Enum.GetValues(typeof(Description)))
            {
                GroupDescriptions[d] = new List<int>();
            }

            //var jahu = test().ToList();

            Parallel.ForEach(Sample, smprow =>
            {
                int HashCode = smprow.GetXemlHashCode();
                int index = HashCodes.BinarySearch(HashCode);
                if (index < 0)
                {
                    lock (ReplicaGroup)
                    {
                        index = HashCodes.BinarySearch(HashCode);
                        if (index < 0)
                        {
                            HashCodes.Insert(~index, HashCode);
                            ReplicaGroupRow newReplicaGroupRow = ReplicaGroup.NewReplicaGroupRow();
                            newReplicaGroupRow.FK_Document = smprow.FK_Document;
                            newReplicaGroupRow.HashCode = HashCode;
                            newReplicaGroupRow.Label = "grrr";// smprow.GetXemlDescription(jahu);
                            ReplicaGroup.AddReplicaGroupRow(newReplicaGroupRow);

                            foreach (Description d in Enum.GetValues(typeof(Description)))
                            {
                                GroupDescriptions[d].Add(smprow.GetXemlHashCode(d));
                            }
                        }
                    }
                }
                lock (ReplicaGroupMapping)
                {
                    ReplicaGroupMapping.AddReplicaGroupMappingRow(smprow.FK_Document, smprow.FK_Story, smprow.Id, HashCode);
                }
            });

            //this holds the Description which differ between the replica groups
            List<Description> uniqueDescriptions = new List<Description>();
            foreach (Description d in Enum.GetValues(typeof(Description)))
            {
                if (GroupDescriptions[d].Distinct().Count() > 1)
                {
                    uniqueDescriptions.Add(d);
                }
            }

            //take from each replicagroup the first item as an example and calculate the ReplicaGroupDescription
            foreach (ReplicaGroupRow rg in ReplicaGroup)
            {
                rg.Label = ReplicaGroupMapping.First(x => x.FK_HashCode == rg.HashCode).SampleRowParent.GetXemlDescription(uniqueDescriptions);
            }

            //sort the replica groups and assign the ReplicaGroupIds
            short counter = 1;
            List<XemlDataSet.ReplicaGroupRow> sortedReplica = this.ReplicaGroup.Rows.Cast<XemlDataSet.ReplicaGroupRow>().ToList();
            sortedReplica.Sort(new XemlDataSet.ReplicaComparer());
            foreach (var item in sortedReplica)
            {
                item.Id = counter++;
            }

            // run accept changes on all but OntologyHandlers, as new one's might have been inserted fomr the document
            Experimenter.AcceptChanges();
            Ontology.AcceptChanges();
            DataProvider.AcceptChanges();
            ExperimentKeywords.AcceptChanges();
            Story.AcceptChanges();
            SampleMapping.AcceptChanges();
            ReplicaGroup.AcceptChanges();
            ReplicaGroupMapping.AcceptChanges();
            Document.AcceptChanges();
        }

        public XemlDataSet(Guid XemlId, IDataProvider dataProvider)
            : this(dataProvider)
        {
            this.Fill(XemlId);
        }

        public XemlDataSet(SqlDataReader reader)
            : this()
        {
            Fill(reader);
        }

        public XemlDataSet(IDataProvider dataProvider)
            : this()
        {
            this._dataProvider = dataProvider;
        }

        private void BeginLoadData()
        {
            foreach (DataTable tbl in base.Tables)
            {
                tbl.BeginLoadData();
            }
        }

        private void EndLoadData()
        {
            foreach (DataTable tbl in base.Tables)
            {
                tbl.EndLoadData();
            }
        }

        /// <summary>
        /// this method saves the current document in a relational database
        /// the schema of the database is under development and might change in this phase of development without further notice
        /// </summary>
        //public static bool Save(XemlDocument xemlDocument, string connectionString, Func<string, Guid, bool> feedback)
        //{
        //    using (MSSQLXemlDataSet ds = new MSSQLXemlDataSet(xemlDocument))
        //    using (SqlConnection connection = new SqlConnection(connectionString))
        //    {
        //        try
        //        {
        //            connection.Open();
        //            using (SqlTransaction trans = connection.BeginTransaction())
        //            {
        //                try
        //                {
        //                    bool res = ds.BulkCopy(xemlDocument.Id, connection, trans, feedback);
        //                    //trans.Commit();
        //                    return res;
        //                }
        //                catch (DbException dbex)
        //                {
        //                    RollbackTransaction(trans, dbex);
        //                    throw new Exception(string.Format(CultureInfo.InvariantCulture, "caught database Exception while publishing to {0} (transaction rolled back, no changes were made)", connection.Database), dbex);
        //                }
        //                catch (Exception ex)
        //                {
        //                    RollbackTransaction(trans, ex);
        //                    throw new Exception(string.Format(CultureInfo.InvariantCulture, "caught general Exception while publishing to {0} (transaction rolled back, no changes were made)", connection.Database), ex);
        //                }
        //            }
        //        }
        //        catch (Exception)
        //        {
        //            throw;
        //        }
        //        finally
        //        {
        //            connection.Close();
        //        }
        //    }
        //}

        //public static IXemlDataSet GetDataSetProvider(IDataProvider dataProvider, XemlDocument xemlDocument) 
        //{
        //    String dataProviderTypeName = dataProvider.GetType().FullName;
        //    IXemlDataSet ds;
        //    switch (dataProviderTypeName) {
        //        default:
        //        case "XemlUtilities.MSSQLXemlDataSet": ds = new MSSQLXemlDataSet(xemlDocument); break;
        //        case "XemlUtilities.MySQLXemlDataSet": ds = new MySQLXemlDataSet(xemlDocument); break;
        //    }
        //    return ds;
        //}

        /// <summary>
        /// this method saves the current document in a relational database
        /// the schema of the database is under development and might change in this phase of development without further notice
        /// </summary>
        public static bool Save(XemlDocument xemlDocument, IDataProvider dataProvider, Func<string, Guid, bool> feedback)
        {
            using (XemlDataSet ds = XemlDataSet.GetInstance(xemlDocument, dataProvider))
            using (IDbConnection connection = dataProvider.BuildConnection())
            {
                try
                {
                    connection.Open();
                    using (IDbTransaction trans = connection.BeginTransaction())
                    {
                        try
                        {
                            bool res = ds.BulkCopy(xemlDocument.Id, connection, trans, feedback);
                            trans.Commit(); // TODO reenable
                            return res;
                        }
                        catch (DbException dbex)
                        {
                            RollbackTransaction(trans, dbex);
                            throw new Exception(string.Format(CultureInfo.InvariantCulture, "caught database Exception while publishing to {0} (transaction rolled back, no changes were made)", connection.Database), dbex);
                        }
                        catch (Exception ex)
                        {
                            RollbackTransaction(trans, ex);
                            throw new Exception(string.Format(CultureInfo.InvariantCulture, "caught general Exception while publishing to {0} (transaction rolled back, no changes were made)", connection.Database), ex);
                        }
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private static void RollbackTransaction(IDbTransaction trans, Exception ex)
        {
            // Attempt to roll back the transaction.
            try
            {
                trans.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                throw new Exception(string.Format("Rollback Exception Type: {0}", ex2.GetType()), ex);
            }
        }

        public bool BulkCopy(Guid xemlId, IDbConnection connection, IDbTransaction transaction, Func<string, Guid, bool> feedback)
        {
            // first see if we have the experimenter in the DB already
            DocumentRow documentRow = Document.Single();
            if (!Experimenter.Exists(this, documentRow.ExperimenterEmail, connection, transaction))
            {
                Experimenter.BulkCopy(this, connection, transaction);
            }

            //try to find the document in the store based on the XemlId (GUID)
            short? NewIdentityId = Document.GetInternalId(xemlId, connection, transaction);
            if (NewIdentityId.HasValue)
            {
                if (feedback(Document.Single().Name, Document.Single().XemlId))
                {
                    List<DataTable> dataTables = new List<DataTable>() { 
                        Annotation, ExperimentKeywords, SampleMapping,
                        Material, DevelopmentalStage, ReplicaGroupMapping,
                        ReplicaGroup, Partition, Observation, ObservationPoint,
                        Individual, IndividualsPool, Story, Term, DataProvider, Ontology
                    };
                    DocumentDataTable.PurgeDetails(dataTables, connection, transaction, NewIdentityId.Value);
                    DocumentRow row = Document.Single(x => x.XemlId == xemlId);
                    row.Id = NewIdentityId.Value;
                    Document.Update(row, connection, transaction, NewIdentityId.Value);
                }
                else
                {
                    return false;
                }
            }
            else
            {
                NewIdentityId = Document.Insert(Document.Single(x => x.XemlId == xemlId), connection, transaction);
                Document.Single(x => x.XemlId == xemlId).Id = NewIdentityId.Value;
            }

            Story.BulkCopy(this, connection, transaction);
            ExperimentKeywords.BulkCopy(this, connection, transaction);
            OntologyHandler.InsertNewRowsToDb(connection, transaction);
            Ontology.BulkCopy(this, connection, transaction);
            IndividualsPool.BulkCopy(this, connection, transaction);
            Individual.BulkCopy(this, connection, transaction);
            ObservationPoint.BulkCopy(this, connection, transaction);
            Observation.BulkCopy(this, connection, transaction);
            Sample.BulkCopy(this, connection, transaction);
            Partition.BulkCopy(this, connection, transaction);
            Event.BulkCopy(this, connection, transaction);
            Annotation.BulkCopy(this, connection, transaction);
            Term.BulkCopy(this, connection, transaction);
            Value.BulkCopy(this, connection, transaction);
            DataProvider.BulkCopy(this, connection, transaction);
            Material.BulkCopy(this, connection, transaction);
            DevelopmentalStage.BulkCopy(this, connection, transaction);
            SampleMapping.BulkCopy(this, connection, transaction);
            ReplicaGroup.BulkCopy(this, connection, transaction);
            ReplicaGroupMapping.BulkCopy(this, connection, transaction);

            return true;
        }

        public virtual void Fill(Guid XemlId)
        {
            throw new NotImplementedException("you are not supposed to instantiate this class");
        }

        protected void Fill(DbDataReader reader)
        {
            //BeginLoadData();

            //each Load()-call sets the reader to the next result, no need to call NextResult()
            Experimenter.Load(reader);
            Document.Load(reader);
            Story.Load(reader);
            ExperimentKeywords.Load(reader);
            OntologyHandler.Load(reader);
            Ontology.Load(reader);
            IndividualsPool.Load(reader);
            Individual.Load(reader);
            ObservationPoint.Load(reader);
            Observation.Load(reader);
            Sample.Load(reader);
            Partition.Load(reader);
            Event.Load(reader);
            Annotation.Load(reader);
            Term.Load(reader);
            Value.Load(reader);
            DataProvider.Load(reader);
            Material.Load(reader);
            DevelopmentalStage.Load(reader);
            SampleMapping.Load(reader);
            ReplicaGroup.Load(reader);
            ReplicaGroupMapping.Load(reader);

            EndLoadData();
        }

        public static string LoadXeml(Guid xemlId, string connectionString)
        {
            using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
            using (cmd.Connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                cmd.CommandText = @"SELECT XEML FROM xeml.[Document] WHERE XemlId = @xemlId";
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@xemlId", xemlId);

                try
                {
                    cmd.Connection.Open();
                    return (string)cmd.ExecuteScalar();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }

        /// <summary>
        /// resolves the GUID id into the internal id
        /// </summary>
        public static short GetInternalId(Guid XemlId, IDataProvider dataProvider)
        {
            using (IDbConnection connection = dataProvider.BuildConnection())
            using (IDbCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = string.Concat("SELECT id FROM ", xemlSchemaName, ".Document WHERE XemlId = @XemlId;");

                IDbDataParameter xemlId = cmd.CreateParameter();
                xemlId.ParameterName = "@XemlId";
                xemlId.Value = XemlId;
                cmd.Parameters.Add(xemlId);

                try
                {
                    cmd.Connection.Open();
                    return (short)cmd.ExecuteScalar();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }

        public static void PurgeTable(DataTable table, IDbConnection connection, IDbTransaction trans, short DocumentId)
        {
            PurgeTable(table.TableName, connection, trans, DocumentId);
        }

        public static void PurgeTable(string tableName, IDbConnection connection, IDbTransaction trans, short DocumentId)
        {
            using (IDbCommand cmd = connection.CreateCommand())
            {
                cmd.Transaction = trans;

                cmd.CommandText = string.Concat("DELETE FROM ", xemlSchemaName, ".", tableName, " WHERE FK_Document = @FK_Document;");
                cmd.CommandType = System.Data.CommandType.Text;

                IDataParameter param = cmd.CreateParameter();
                param.ParameterName = "@FK_Document";
                //param.DbType = DbType.Int16;
                param.Value = DocumentId;
                cmd.Parameters.Add(param);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        #region tables section
        partial class StoryDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, TreeNode<StoryBase> node)
            {
                StoryBase story = node.Content;

                StoryRow row = this.NewStoryRow();
                row.FK_Document = DocumentId;
                row.Id = Convert.ToInt16(node.Index);

                row.Label = XmlLexicalType.ConvertToToken(story.Label);

                if (story is StorySplit)
                {
                    row.TimePoint = (node.Content as StorySplit).TimePoint.TotalSeconds;
                    row.FK_ParentStoryId = Convert.ToInt16(node.Parent.Index);
                }

                row.AssignAnnotation(story);
                this.AddStoryRow(row);

                if (story is Story)
                {
                    (this.DataSet as XemlDataSet).IndividualsPool.Add(DocumentId, Convert.ToInt16(node.Root.Index), (story as Story).Pools);
                }

                (this.DataSet as XemlDataSet).Term.Add(DocumentId, row.Id, story.Variables);

                (this.DataSet as XemlDataSet).Sample.Add(DocumentId, row.Id, story.Samples);

                (this.DataSet as XemlDataSet).ObservationPoint.Add(DocumentId, row.Id, story.ObservationPoints);

                (this.DataSet as XemlDataSet).Event.Add(DocumentId, row.Id, story.Events);

                this.Add(DocumentId, node.SubNodes(false));
            }

            internal void Add(short documentId, IEnumerable<TreeNode<StoryBase>> nodes)
            {
                foreach (TreeNode<StoryBase> node in nodes)
                {
                    Add(documentId, node);
                }
            }

            //this method is not used yet
            internal IEnumerable<short> GetAllParentStories(short story)
            {
                StoryRow current = this.Single(x => x.Id == story);

                if (!current.IsFK_ParentStoryIdNull())
                {
                    foreach (short substory in GetAllParentStories(current.FK_ParentStoryId))
                    {
                        yield return substory;
                    }
                }
                yield return current.Id;
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class AnnotationDataTable : IDataTableBase
        {
            int safe_count;

            public override void BeginInit()
            {
                base.BeginInit();
                safe_count = short.MinValue;
            }

            private short? Add(short DocumentId, IEnumerable<TaggedAnnotation> annotations)
            {
                short currentId = Convert.ToInt16(Interlocked.Increment(ref safe_count));
                byte counter = 0;
                foreach (TaggedAnnotation kvp in annotations)
                {
                    if (!String.IsNullOrEmpty(kvp.Annotation))
                    {
                        lock (this.Rows.SyncRoot)
                        {
                            AnnotationRow row = this.NewAnnotationRow();
                            row.FK_Document = DocumentId;
                            row.Id = currentId;
                            row.SubId = ++counter;
                            row.Tag = kvp.Tag;
                            row.Annotation = kvp.Annotation;
                            this.AddAnnotationRow(row);
                        }
                    }
                }

                if (counter > 0)
                {
                    return currentId;
                }
                else
                {
                    return null;
                }
            }

            internal short? Add(short DocumentId, Sample s)
            {
                return this.Add(DocumentId, s.Annotations);
            }

            internal short? Add(short DocumentId, ObservationPoint op)
            {
                return this.Add(DocumentId, op.Annotations);
            }

            internal short? Add(short DocumentId, StoryBase s)
            {
                return this.Add(DocumentId, s.Annotations);
            }

            internal short? Add(short DocumentId, IndividualsPool ip)
            {
                return this.Add(DocumentId, ip.Annotations);
            }

            internal short? Add(short DocumentId, Individual ind)
            {
                return this.Add(DocumentId, ind.Annotations);
            }

            internal short? Add(short DocumentId, Event ev)
            {
                return this.Add(DocumentId, ev.Annotations);
            }

            internal short? Add(short DocumentId, Observation ob)
            {
                return this.Add(DocumentId, ob.Annotations);
            }

            internal short? Add(short DocumentId, Partition part)
            {
                return this.Add(DocumentId, part.Annotations);
            }

            internal short? Add(short DocumentId, Term term)
            {
                return this.Add(DocumentId, term.Annotations);
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class DocumentDataTable : IDataTableBase
        {
            public static string UriTemplate = "http://gmd.mpimp-golm.mpg.de/profile/default.aspx?XemlId={0}";

            internal short Add(XemlDocument xemlDocument)
            {
                // Add some new rows to the collection. 
                DocumentRow row = this.NewDocumentRow();
                row.Id = 1;
                row.XemlId = xemlDocument.Id;
                if (!string.IsNullOrEmpty(xemlDocument.ExperimentHeader.Experimenter.Email))
                {
                    row.ExperimenterEmail = xemlDocument.ExperimentHeader.Experimenter.Email;
                }
                if (!string.IsNullOrEmpty(xemlDocument.ExperimentHeader.Experimenter.FirstName))
                {
                    row.ExperimenterFirstName = xemlDocument.ExperimentHeader.Experimenter.FirstName;
                }
                if (!string.IsNullOrEmpty(xemlDocument.ExperimentHeader.Experimenter.LastName))
                {
                    row.ExperimenterLastName = xemlDocument.ExperimentHeader.Experimenter.LastName;
                }
                if (!string.IsNullOrEmpty(xemlDocument.ExperimentHeader.Experimenter.Organisation))
                {
                    row.Organisation = xemlDocument.ExperimentHeader.Experimenter.Organisation;
                }
                row.Name = xemlDocument.Name;
                row.StartDate = xemlDocument.StartDate;
                row.ObservationTime = xemlDocument.ObservationTime.TotalSeconds;
                row.Description = xemlDocument.ExperimentHeader.Description;
                row.Xeml = xemlDocument.Xeml;
                row.XemlVersion = XemlDocument.XemlVersion;
                row.NameSpace = XemlDocument.xmlNameSpace;

                this.AddDocumentRow(row);

                return row.Id;
            }

            internal static void PurgeDetails(List<DataTable> tables, IDbConnection connection, IDbTransaction trans, short DocumentId)
            {
                foreach (DataTable dataTable in tables)
                {
                    XemlDataSet.PurgeTable(dataTable, connection, trans, DocumentId);
                }
            }

            /// <summary>
            /// Attach the insert function for the Document DataTable in the derived XemlDataSet classes.
            /// </summary>
            /// <param name="row">The Row to save</param>
            /// <param name="connection"></param>
            /// <param name="trans"></param>
            public delegate short DocumentInsert(DocumentRow row, IDbConnection connection, IDbTransaction trans);
            public delegate void DocumentUpdate(DocumentRow row, IDbConnection connection, IDbTransaction trans, short internalId);

            public DocumentInsert Insert;
            public DocumentUpdate Update;

            internal short? GetInternalId(Guid documentId, IDbConnection connection, IDbTransaction trans)
            {
                using (IDbCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = string.Concat("SELECT id FROM ", xemlSchemaName, @".Document WHERE XemlId = @XemlId;");
                    cmd.CommandType = System.Data.CommandType.Text;

                    IDbDataParameter xemlIdParam = cmd.CreateParameter();
                    xemlIdParam.ParameterName = "@XemlId";
                    xemlIdParam.Value = documentId;
                    xemlIdParam.Direction = ParameterDirection.Input;
                    cmd.Parameters.Add(xemlIdParam);

                    try
                    {
                        cmd.Transaction = trans;
                        using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            if (reader.Read())
                            {
                                return reader.GetInt16(0) as short?;
                            }
                            return null;
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class ExperimenterDataTable : IDataTableBase
        {

            internal bool Exists(XemlDataSet xds, String email, IDbConnection connection, IDbTransaction transaction)
            {
                return xds.ExperimenterExists(email, connection, transaction);
            }

            /// <summary>
            /// adds an experimenter
            /// </summary>
            internal void Add(String email, String firstName, String LastName, String organisation)
            {
                ExperimenterRow row = this.NewExperimenterRow();
                row.Email = email;
                row.FirstName = firstName;
                row.LastName = LastName;
                row.Organisation = organisation;
                this.AddExperimenterRow(row);
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = false)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class ExperimentKeywordsDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, IEnumerable<Keyword> Keywords)
            {
                foreach (var item in Keywords)
                {
                    ExperimentKeywordsRow row = this.NewExperimentKeywordsRow();
                    row.FK_Document = DocumentId;
                    row.Keyword = item.Value;
                    this.AddExperimentKeywordsRow(row);
                }
            }

            internal void BulkCopy(SqlConnection connection, SqlTransaction trans)
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.CheckConstraints, trans))
                {
                    bulkCopy.BatchSize = BulkCopyBatchSize;
                    bulkCopy.BulkCopyTimeout = BulkCopyTimeout;
                    try
                    {
                        bulkCopy.DestinationTableName = string.Concat(xemlSchemaName, ".", this.TableName);
                        bulkCopy.ColumnMappings.Add(this.FK_DocumentColumn.ColumnName, "FK_Document");
                        bulkCopy.ColumnMappings.Add(this.KeywordColumn.ColumnName, "Keyword");

                        Debug.WriteLine(string.Format(CultureInfo.InvariantCulture, "attempt to bulkcopy '{0}'", bulkCopy.DestinationTableName));
                        bulkCopy.WriteToServer(this);
                    }
                    catch (SqlException ex)
                    {
                        if (ex.Class == 14 && ex.Number == 2627)
                        {
                            //Violation of PRIMARY KEY constraint 'PK_Experimenter'. Cannot insert duplicate key in object 'xeml.Experimenter'. The duplicate key value is (jahu@gmx.de).
                            //do nothing
                        }
                        else
                        {
                            throw new Exception(string.Format(CultureInfo.InvariantCulture, "SqlException in BulkCopy {0}", bulkCopy.DestinationTableName), ex);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(string.Format(CultureInfo.InvariantCulture, "Exception in BulkCopy {0}", bulkCopy.DestinationTableName), ex);
                    }
                    finally
                    {
                        bulkCopy.Close();
                    }
                }
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class TermDataTable : IDataTableBase
        {
            /// <summary>
            /// adds a Term
            /// </summary>
            internal short Add(short DocumentId, Term t)
            {
                byte NameSpaceId = (this.DataSet as XemlDataSet).Ontology.GetNameSpaceId(t.NameSpaceAlias);
                lock (this.Rows.SyncRoot)
                {
                    TermRow row = this.FirstOrDefault(x => x.FK_Ontology == NameSpaceId && x.TermId == t.TermId);
                    if (row == null)
                    {
                        row = this.NewTermRow();
                        row.FK_Document = DocumentId;
                        row.FK_Ontology = NameSpaceId;
                        row.Id = Convert.ToInt16(this.AsParallel().AsUnordered().Count() + 1);
                        row.TermId = t.TermId;
                        row.Name = t.Name;
                        row.AssignAnnotation(t);
                        this.AddTermRow(row);
                    }
                    return row.Id;
                }
            }

            internal short Add(short DocumentId, short StoryId, short? PartitionId, VariableTerm t)
            {
                short TermId = this.Add(DocumentId, t as Term);
                (this.DataSet as XemlDataSet).Value.Add(DocumentId, StoryId, PartitionId, TermId, t.Values);
                return TermId;
            }

            internal short Add(short DocumentId, short StoryId, short? PartitionId, DynamicTerm t)
            {
                short TermId = this.Add(DocumentId, t as Term);
                (this.DataSet as XemlDataSet).Value.Add(DocumentId, StoryId, PartitionId, TermId, t.Values);
                return TermId;
            }

            internal void Add(short DocumentId, short StoryId, VariableCollection variableCollection)
            {
                foreach (DynamicTerm variable in variableCollection)
                {
                    this.Add(DocumentId, StoryId, null, variable);
                }
            }

            internal void Add(short DocumentId, short StoryId, short PartitionId, PositionCollection positionCollection)
            {
                foreach (VariableTerm position in positionCollection)
                {
                    this.Add(DocumentId, StoryId, PartitionId, position);
                }
            }


            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class ValueDataTable : IDataTableBase
        {
            int safe_TotalCount;
            int safe_CycleCount;

            public override void BeginInit()
            {
                base.BeginInit();
                safe_TotalCount = short.MinValue;
                safe_CycleCount = short.MinValue;
            }

            private void Add(short DocumentId, short StoryId, short? PartitionId, short TermID, short? CycleId, TimeSpan? time, string Context, string value, string Unit, string Label, TimeSpan? Duration)
            {
                lock (this.Rows.SyncRoot)
                {
                    ValueRow row = this.NewValueRow();
                    row.FK_Document = DocumentId;
                    row.Id = Convert.ToInt16(Interlocked.Increment(ref safe_TotalCount));
                    row.FK_Story = StoryId;
                    if (PartitionId.HasValue)
                    {
                        row.FK_Partition = PartitionId.Value;
                    }
                    row.FK_Term = TermID;
                    if (time.HasValue)
                    {
                        row.Time = time.Value.TotalSeconds;
                    }
                    if (CycleId.HasValue)
                    {
                        row.CycleId = CycleId.Value;
                    }
                    row.Context = Context;
                    row.Value = value;
                    if (!string.IsNullOrEmpty(Unit))
                    {
                        row.Unit = Unit;
                    }
                    if (!string.IsNullOrEmpty(Label))
                    {
                        row.Label = Label;
                    }
                    if (Duration.HasValue)
                    {
                        row.Duration = Duration.Value.TotalSeconds;
                    }
                    this.AddValueRow(row);
                }
            }

            private void Add(short DocumentId, short StoryId, short? PartitionId, short TermId, DynamicValue v)
            {
                Add(DocumentId, StoryId, PartitionId, TermId, null, v.Time, v.Context, v.Val, v.Unit, v.Label, null);
            }

            private void Add(short DocumentId, short StoryId, short? PartitionId, short TermId, Value v)
            {
                Add(DocumentId, StoryId, PartitionId, TermId, null, null, v.Context, v.Val, v.Unit, v.Label, null);
            }

            private void Add(short DocumentId, short StoryId, short? PartitionId, short TermId, Cycle c)
            {
                short CycleSubId = Convert.ToInt16(Interlocked.Increment(ref safe_CycleCount));
                foreach (DynamicValue x in c.Values)
                {
                    Add(DocumentId, StoryId, PartitionId, TermId, CycleSubId, c.Time, c.Context, x.Val, c.Unit, c.Label, x.Time);
                }
            }

            internal void Add(short DocumentId, short StoryId, short? PartitionId, short TermId, IEnumerable<ValueBase> valueCollection)
            {
                foreach (ValueBase vb in valueCollection)
                {
                    if (vb is DynamicValue)
                    {
                        this.Add(DocumentId, StoryId, PartitionId, TermId, vb as DynamicValue);
                    }
                    else if (vb is Cycle)
                    {
                        this.Add(DocumentId, StoryId, PartitionId, TermId, vb as Cycle);
                    }
                    else if (vb is Value)
                    {
                        this.Add(DocumentId, StoryId, PartitionId, TermId, vb as Value);
                    }
                }
            }

            public IList<ValueRow> GetLatestValues(short storyId, short termId, double time)
            {
                if (time < 0)
                {
                    throw new ArgumentOutOfRangeException("time", "time must be positive");
                }

                ValueRow res = this.Where(x => x.FK_Story == storyId && x.FK_Term == termId && x.Time <= time).OrderByDescending(x => x.Time).FirstOrDefault();

                if (res != null)
                {
                    // return all values from the same cycle
                    IList<ValueRow> valueRows = this.Where(x => x.FK_Term == res.FK_Term && x.Time == res.Time && x.CycleId == res.CycleId).OrderBy(x => x.Id).ToList<ValueRow>();
                    return valueRows;
                }
                else
                {
                    //try towards the tree root
                    StoryRow currentStory = (this.DataSet as XemlDataSet).Story.Single(x => x.Id == storyId);
                    if (!currentStory.IsFK_ParentStoryIdNull())
                    {
                        return GetLatestValues(currentStory.FK_ParentStoryId, termId, Math.Min(time, currentStory.TimePoint));
                    }
                }

                //nothing found
                return null;
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class ObservationPointDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short StoryId, ObservationPointCollection collection)
            {
                foreach (ObservationPoint item in collection)
                {
                    Add(DocumentId, StoryId, item);
                }
            }

            internal void Add(short DocumentId, short StoryId, ObservationPoint op)
            {
                ObservationPointRow row = this.NewObservationPointRow();
                row.FK_Document = DocumentId;
                row.FK_Story = StoryId;
                row.Id = op.Id;
                row.TargetTime = op.TimePoint.TotalSeconds;
                row.AssignAnnotation(op);
                this.AddObservationPointRow(row);

                (this.DataSet as XemlDataSet).Observation.Add(DocumentId, StoryId, op.Id, op.Observations);
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class ObservationDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short StoryId, int ObservationPointId, IEnumerable<Observation> observations)
            {
                Parallel.ForEach(observations, ob =>
                {
                    short IndividualsPool = (this.DataSet as XemlDataSet).IndividualsPool.GetId(ob.IndividualsPool.NameSpace, ob.IndividualsPool.GermPlasm);
                    lock (this.Rows.SyncRoot)
                    {
                        ObservationRow row = this.NewObservationRow();
                        row.FK_Document = DocumentId;
                        row.Id = ob.Id;
                        row.FK_Story = StoryId;
                        row.FK_ObservationPoint = ObservationPointId;
                        row.FK_IndividualsPool = IndividualsPool;
                        if (ob.Individual != null)
                        {
                            row.FK_Individual = ob.Individual.Id;
                        }

                        row.Duration = ob.Duration.TotalSeconds;
                        row.IndividualInfluence = ob.IndividualInfluence;
                        row.Destructive = ob.Destructive;
                        row.AssignAnnotation(ob);
                        this.AddObservationRow(row);
                    }

                    (this.DataSet as XemlDataSet).Partition.Add(DocumentId, StoryId, ob.Id, ob.Partitions);

                    (this.DataSet as XemlDataSet).DevelopmentalStage.Add(DocumentId, StoryId, ob.Id, ob.DevelopmentalStages);
                });
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class SampleDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short StoryId, SampleCollection collection)
            {
                foreach (Sample sample in collection)
                {
                    Add(DocumentId, StoryId, sample);
                }
            }

            internal void Add(short DocumentId, short StoryId, Sample s)
            {
                SampleRow row = this.NewSampleRow();
                row.FK_Document = DocumentId;
                row.FK_Story = StoryId;
                row.Id = s.Id;
                row.TimeIntermix = Enum.GetName(typeof(TimeIntermix), s.TimeIntermix);
                row.AssignAnnotation(s);
                this.AddSampleRow(row);
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class PartitionDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short StoryId, int ObservationId, IEnumerable<Partition> partitions)
            {
                foreach (Partition partition in partitions)
                {
                    int sample = partition.GetSample().Id;

                    lock (this.Rows.SyncRoot)
                    {
                        PartitionRow row = this.NewPartitionRow();
                        row.FK_Document = DocumentId;
                        row.FK_Story = StoryId;
                        row.Id = Convert.ToInt16(partition.Id);
                        row.FK_Sample = sample;
                        row.FK_Observation = ObservationId;
                        row.AssignAnnotation(partition);
                        this.AddPartitionRow(row);
                    }
                }

                foreach (Partition partition in partitions)
                {
                    //add material
                    (this.DataSet as XemlDataSet).Material.Add(DocumentId, StoryId, Convert.ToInt16(partition.Id), partition.Material);

                    //add positions
                    (this.DataSet as XemlDataSet).Term.Add(DocumentId, StoryId, Convert.ToInt16(partition.Id), partition.Positions);
                }
            }

            internal void BulkCopy(SqlConnection connection, SqlTransaction trans)
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.CheckConstraints, trans))
                {
                    bulkCopy.BatchSize = BulkCopyBatchSize;
                    bulkCopy.BulkCopyTimeout = BulkCopyTimeout;
                    try
                    {
                        bulkCopy.DestinationTableName = string.Concat(xemlSchemaName, ".", this.TableName);
                        bulkCopy.ColumnMappings.Add(this.FK_DocumentColumn.ColumnName, "FK_Document");
                        bulkCopy.ColumnMappings.Add(this.FK_StoryColumn.ColumnName, "FK_Story");
                        bulkCopy.ColumnMappings.Add(this.IdColumn.ColumnName, "Id");
                        bulkCopy.ColumnMappings.Add(this.FK_SampleColumn.ColumnName, "FK_Sample");
                        bulkCopy.ColumnMappings.Add(this.FK_ObservationColumn.ColumnName, "FK_Observation");
                        bulkCopy.ColumnMappings.Add(this.AnnotationIdColumn.ColumnName, "AnnotationId");

                        Debug.WriteLine(string.Format(CultureInfo.InvariantCulture, "attempt to bulkcopy '{0}'", bulkCopy.DestinationTableName));
                        bulkCopy.WriteToServer(this);
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                    finally
                    {
                        bulkCopy.Close();
                    }
                }
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class EventDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short StoryId, EventCollection collection)
            {
                foreach (Event item in collection)
                {
                    Add(DocumentId, StoryId, item);
                }
            }

            internal void Add(short DocumentId, short StoryId, Event e)
            {
                EventRow row = this.NewEventRow();
                row.FK_Document = DocumentId;
                row.FK_Story = StoryId;
                row.TimePoint = e.TimePoint.TotalSeconds;
                row.Label = e.Label;
                row.AssignAnnotation(e);
                this.AddEventRow(row);
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class IndividualsPoolDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short BaseStoryId, IEnumerable<IndividualsPool> pools)
            {
                foreach (IndividualsPool ip in pools)
                {
                    IndividualsPoolRow row = this.NewIndividualsPoolRow();
                    row.FK_Document = DocumentId;
                    row.FK_BaseStory = BaseStoryId;
                    row.Id = Convert.ToInt16(this.AsParallel().AsUnordered().Count() + 1);
                    row.NS = ip.NameSpace;
                    row.GermPlasm = XmlLexicalType.ConvertToToken(ip.GermPlasm);
                    row.AssignAnnotation(ip);
                    this.AddIndividualsPoolRow(row);

                    (this.DataSet as XemlDataSet).Individual.Add(DocumentId, BaseStoryId, row.Id, ip.Individuals);
                }
            }

            internal short GetId(string NameSpace, string GermPlasm)
            {
                return this.Single(x => x.NS == NameSpace && x.GermPlasm == GermPlasm).Id;
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class IndividualDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short StoryId, short PoolId, IndividualsCollection individualsCollection)
            {
                foreach (Individual ind in individualsCollection)
                {
                    IndividualRow row = this.NewIndividualRow();
                    row.FK_Document = DocumentId;
                    row.Id = ind.Id;
                    row.FK_IndividualsPool = PoolId;
                    row.AssignAnnotation(ind);
                    this.AddIndividualRow(row);
                }
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class OntologyDataTable : IDataTableBase
        {
            int safeCount;

            internal byte GetNameSpaceId(string NameSpaceAlias)
            {
                return this.Single(x => ((x != null) && (x.NameSpaceAlias == NameSpaceAlias))).Id;
            }

            internal void Add(short DocumentId, IEnumerable<OntologyHandlerResource> ontologies)
            {
                foreach (OntologyHandlerResource item in ontologies)
                {
                    OntologyRow row = this.NewOntologyRow();
                    row.FK_Document = DocumentId;
                    row.Id = Convert.ToByte(Interlocked.Increment(ref safeCount));
                    if (!String.IsNullOrEmpty(item.NameSpaceAlias))
                    {
                        row.NameSpaceAlias = item.NameSpaceAlias;
                    }

                    /* 
                     * an exception in this next line refers to the fact, that the managing ontology handler is missing in the Ontology Hanlder Table.
                     * Each implemented Ontolgy Handler needs to be added into the EndInit(...) Method of the OntologyHandlerDataTable Table within this DataSet
                     * 
                     */
                    row.FK_OntologyHandler = (this.DataSet as XemlDataSet).OntologyHandler.Single(x => x.Uri == item.Uri).Id;
                    row.InstanceLocation = item.Location;
                    this.AddOntologyRow(row);
                }
                return;
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class DataProviderDataTable : IDataTableBase
        {
            int safe_count;

            public override void BeginInit()
            {
                base.BeginInit();
                safe_count = 0;
            }

            internal void Add(short documentId, ProviderResourceCollection providerResourceCollection)
            {
                foreach (DataProviderResource item in providerResourceCollection)
                {
                    DataProviderRow row = this.NewDataProviderRow();
                    row.FK_Document = documentId;
                    row.Id = Convert.ToByte(Interlocked.Increment(ref safe_count));
                    row.Uri = item.Uri;
                    row.Name = item.FriendlyName;
                    this.AddDataProviderRow(row);
                }
                return;
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class OntologyHandlerDataTable
        {
            internal short Add(string uri)
            {
                lock (this.Rows.SyncRoot)
                {
                    OntologyHandlerRow row = this.FirstOrDefault(x => x.Uri == uri);
                    if (row == null)
                    {
                        row = this.NewOntologyHandlerRow();
                        row.Id = Convert.ToByte(this.AsParallel().AsUnordered().Count() + 1);
                        row.Uri = uri;
                        this.AddOntologyHandlerRow(row);
                    }
                    return row.Id;
                }
            }

            public void LoadFromDataProvider(IDataProvider dataProvider)
            {
                using (IDbConnection con = dataProvider.BuildConnection())
                using (IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = String.Concat("SELECT id, uri FROM ", xemlSchemaName, ".", this.TableName);

                    try
                    {
                        con.Open();
                        this.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
                        this.AcceptChanges(); // if we commit later on, we should skip these rows
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }

            public void InsertNewRowsToDb(IDbConnection connection, IDbTransaction transaction)
            {
                var rows = this.AsEnumerable().Where(row => row.RowState == DataRowState.Added).ToList();

                if (rows.Count() > 0)
                {
                    //TODO
                    //implement a way to submit a new Uri and use the identy column value generated from the db, catch the constraintexception in case the Uri is allready present.

                    //using (IDbCommand cmd = connection.CreateCommand())
                    //{
                    //    // TODO set a limit on how many value-pair one can add and output the query in chunks
                    //    List<string> columnNames = this.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList<string>();
                    //    StringBuilder queryBuilder = new StringBuilder(String.Concat("INSERT INTO ", xemlSchemaName, ".", this.TableName, " (", String.Join(", ", columnNames), ") VALUES "));
                    //    String ColumnNamesParameters = String.Concat("(@_{0}_", String.Join(", @_{0}_", columnNames), ")");
                    //    int i = 0;
                    //    foreach (var row in rows)
                    //    {
                    //        foreach (DataColumn column in this.Columns)
                    //        {
                    //            IDbDataParameter param = cmd.CreateParameter();
                    //            param.ParameterName = String.Format("@_{0}_{1}", i, column.ColumnName);
                    //            param.Value = row[column];
                    //            cmd.Parameters.Add(param);
                    //        }
                    //        i++;
                    //    }
                    //    String placeholders = String.Join(", ", GetValuesPlaceHolders(ColumnNamesParameters, --i));
                    //    queryBuilder.Append(placeholders);
                    //    cmd.CommandText = queryBuilder.ToString();
                    //    cmd.Transaction = transaction;
                    //    cmd.ExecuteNonQuery();
                    //}
                }
            }

            private IEnumerable<String> GetValuesPlaceHolders(String template, int max)
            {
                for (int i = 0; i <= max; i++)
                {
                    yield return String.Format(template, i);
                }
            }

            //public override void EndInit()
            //{
            //    base.EndInit();
            //    /*
            //     * CAUTION!!!!
            //     * Do not edit the IDs of Ontology Handlers allready present in this list here, because then you mix up used IDS in the database
            //     * just add new Ontolgy Handlers to the end of this list and increase the ID by one (+1)
            //     * 
            //     * This IDs must correspond to the IDs within the database
            //     */

            //    this.AddOntologyHandlerRow(1, Xeml.Document.Ontologies.OBO.XeoHandler.URI);
            //    this.AddOntologyHandlerRow(2, Xeml.Document.Ontologies.OBO.PEOHandler.URI);
            //    this.AddOntologyHandlerRow(3, Xeml.Document.Ontologies.OBO.EnvOHandler.URI);
            //    this.AddOntologyHandlerRow(4, Xeml.Document.PlantStructure.PlantStructureOntologyHandler.URI);
            //    this.AddOntologyHandlerRow(5, Xeml.Document.DevelopmentalOntologyHandler.URI);
            //    this.AddOntologyHandlerRow(6, Xeml.Document.Ontology.PositioningOntologyHandler.URI);
            //}
        }

        partial class MaterialDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short StoryId, short PartitionId, Term t, short TermId)
            {
                lock (this.Rows.SyncRoot)
                {
                    MaterialRow row = this.NewMaterialRow();
                    row.FK_Document = DocumentId;
                    row.FK_Story = StoryId;
                    row.FK_Partition = PartitionId;
                    row.FK_Term = TermId;
                    row.AssignAnnotation(t);
                    this.AddMaterialRow(row);
                }
                return;
            }

            internal void Add(short DocumentId, short StoryId, short PartitionId, MaterialCollection materialCollection)
            {
                foreach (var material in materialCollection)
                {
                    short termId = (this.DataSet as XemlDataSet).Term.Add(DocumentId, material);
                    this.Add(DocumentId, StoryId, PartitionId, material, termId);
                }
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class DevelopmentalStageDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short StoryId, int ObservationId, Term t, short TermId)
            {
                lock (this.Rows.SyncRoot)
                {
                    DevelopmentalStageRow row = this.NewDevelopmentalStageRow();
                    row.FK_Document = DocumentId;
                    row.FK_Observation = ObservationId;
                    row.FK_Term = TermId;
                    row.AssignAnnotation(t);
                    this.AddDevelopmentalStageRow(row);
                }
                return;
            }

            internal void Add(short DocumentId, short StoryId, int ObservationId, DevelopmentalStageCollection developmentalStageCollection)
            {
                foreach (var stage in developmentalStageCollection)
                {
                    short termId = (this.DataSet as XemlDataSet).Term.Add(DocumentId, stage);
                    this.Add(DocumentId, StoryId, ObservationId, stage, termId);
                }
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class SampleMappingDataTable : IDataTableBase
        {
            internal void Add(short DocumentId, short storyId, int sampleId, string providerName, ProviderDataMappings mappings)
            {
                foreach (SidMapping sid in mappings[providerName].FindAll(sampleId))
                {
                    foreach (string key in sid.ForeignKeyMap.Keys)
                    {
                        SampleMappingRow row = this.NewSampleMappingRow();
                        row.FK_Document = DocumentId;
                        row.FK_Story = storyId;
                        row.FK_Sample = sampleId;
                        row.FK_DataProvider = (this.DataSet as XemlDataSet).DataProvider.Single(x => x.Name == providerName).Id;
                        row.SubKey = key;
                        row.Value = sid.ForeignKeyMap[key];
                        this.AddSampleMappingRow(row);
                    }
                }
                return;
            }

            public IEnumerable<SampleMappingRow> GetChromatograms()
            {
                foreach (var item in this.Where(x => x.SubKey == "Chromatogram" && !string.IsNullOrEmpty(x.Value)).OrderBy(x => x.FK_Sample))
                {
                    yield return item;
                }
                yield break;
            }

            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class ReplicaGroupDataTable : IDataTableBase
        {
            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        partial class ReplicaGroupMappingDataTable : IDataTableBase
        {
            public void BulkCopy(XemlDataSet xds, IDbConnection connection, IDbTransaction transaction, bool throwOnDuplicate = true)
            {
                xds.BulkCopyTable(this, connection, transaction, throwOnDuplicate);
            }
        }

        #endregion tables section

        #region rows section
        partial class DocumentRow
        {
            /// <summary>
            /// removes all InvalidFileNameChars from the name of the document
            /// </summary>
            public string PutativeFileName
            {
                get
                {
                    string res = this.Name;

                    //Path.GetInvalidPathChars();
                    foreach (var item in System.IO.Path.GetInvalidFileNameChars())
                    {
                        res = res.Replace(item, ' ');
                    }
                    res = res.Replace("  ", " ");
                    return res;
                }
            }
        }

        partial class StoryRow
        {
            internal void AssignAnnotation(StoryBase story)
            {
                short? g = (this.tableStory.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, story);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }

            public StoryRow GetBaseStory()
            {
                if (!IsFK_ParentStoryIdNull())
                {
                    return this.tableStory.Single(x => x.Id == this.FK_ParentStoryId).GetBaseStory();
                }
                return this;
            }
        }

        partial class EventRow
        {
            internal void AssignAnnotation(Event e)
            {
                short? g = (this.tableEvent.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, e);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }
        }

        partial class IndividualRow
        {
            internal void AssignAnnotation(Individual ind)
            {
                short? g = (this.tableIndividual.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, ind);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }
        }

        partial class IndividualsPoolRow
        {
            internal void AssignAnnotation(IndividualsPool id)
            {
                short? g = (this.tableIndividualsPool.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, id);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }

            /// <summary>
            /// returns HashCodes from GermPlasm
            /// </summary>
            internal int GetXemlHashCode()
            {
                return GermPlasm.GetHashCode();
            }

            internal string GetXemlDescription()
            {
                return GermPlasm;
            }
        }

        partial class ObservationPointRow
        {
            internal void AssignAnnotation(ObservationPoint op)
            {
                short? g = (this.tableObservationPoint.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, op);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }

            /// <summary>
            /// returns HashCodes from TargetTime and StoryId
            /// </summary>
            internal int GetXemlHashCode()
            {
                return GetXemlHashCode(Description.TargetTime) ^ GetXemlHashCode(Description.StoryId);
            }

            /// <summary>
            /// returns HashCodes from either TargetTime or StoryId
            /// </summary>
            internal int GetXemlHashCode(Description d)
            {
                switch (d)
                {
                    case Description.StoryId:
                        return FK_Story.GetHashCode();
                    case Description.TargetTime:
                        return TargetTime.GetHashCode();
                    default:
                        throw new ArgumentOutOfRangeException("d");
                }
            }
        }

        partial class TermRow
        {
            internal void AssignAnnotation(Term t)
            {
                short? g = (this.tableTerm.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, t);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }

            public string GetIsaExportFormatted()
            {
                return string.Concat(this.TermId, " ", this.Name, " ", (this.tableTerm.DataSet as XemlDataSet).Ontology.Single(x => x.Id == this.FK_Ontology).NameSpaceAlias);
            }

            /// <summary>
            /// returns either HashCode from Material, Position or DevelopmentalStage
            /// </summary>
            internal int GetXemlHashCode()
            {
                return TermId.GetHashCode();
            }

            internal string GetXemlDescription()
            {
                return Name;
            }
        }

        partial class PartitionRow
        {
            internal void AssignAnnotation(Partition p)
            {
                short? g = (this.tablePartition.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, p);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }

            /// <summary>
            /// returns HashCodes from GermPlasm, TargetTime, StoryID, DevelopmentalStage, Material and Position
            /// </summary>
            internal int GetXemlHashCode()
            {
                int res = ObservationRowParent.GetXemlHashCode();

                foreach (MaterialRow m in GetMaterialRows())
                {
                    res ^= m.GetXemlHashCode();
                }

                //process the positions
                OntologyRow PositioningOntology = (this.tablePartition.DataSet as XemlDataSet).Ontology.SingleOrDefault(x => x.NameSpaceAlias == "XPO");
                if (PositioningOntology != null)
                {
                    foreach (TermRow t in (this.tablePartition.DataSet as XemlDataSet).Term.Where(x => x.FK_Document == this.FK_Document && x.FK_Ontology == PositioningOntology.Id))
                    {
                        foreach (ValueRow v in (this.tablePartition.DataSet as XemlDataSet).Value.Where(x => !x.IsFK_PartitionNull()).Where(x => x.FK_Document == this.FK_Document && x.FK_Partition == this.Id && x.FK_Story == this.FK_Story && x.FK_Term == t.Id))
                        {
                            res ^= t.GetXemlHashCode() ^ v.Value.GetHashCode();
                        }
                    }
                }

                return res;
            }

            internal int GetXemlHashCode(Description d)
            {
                switch (d)
                {
                    case Description.StoryId:
                    case Description.TargetTime:
                    case Description.GermPlasm:
                    case Description.DevelopmentalStage:
                        return ObservationRowParent.GetXemlHashCode(d);
                    case Description.Material:
                        int res = 0;
                        foreach (MaterialRow m in GetMaterialRows())
                        {
                            res ^= m.GetXemlHashCode();
                        }
                        return res;
                    case Description.Position:
                        int respos = 0;
                        OntologyRow PositioningOntology = (this.tablePartition.DataSet as XemlDataSet).Ontology.SingleOrDefault(x => x.NameSpaceAlias == "XPO");
                        if (PositioningOntology != null)
                        {
                            foreach (TermRow t in (this.tablePartition.DataSet as XemlDataSet).Term.Where(x => x.FK_Document == this.FK_Document && x.FK_Ontology == PositioningOntology.Id))
                            {
                                foreach (ValueRow v in (this.tablePartition.DataSet as XemlDataSet).Value.Where(x => !x.IsFK_PartitionNull()).Where(x => x.FK_Document == this.FK_Document && x.FK_Partition == this.Id && x.FK_Story == this.FK_Story && x.FK_Term == t.Id))
                                {
                                    respos ^= t.GetXemlHashCode() ^ v.Value.GetHashCode();
                                }
                            }
                        }
                        return respos;
                    default:
                        throw new ArgumentOutOfRangeException("d");
                }
            }

            internal IEnumerable<KeyValuePair<Description, string>> GetXemlDescription()
            {
                foreach (var item in ObservationRowParent.GetXemlDescription())
                {
                    yield return item;
                }

                foreach (var item in GetMaterialRows().Select(x => x.GetXemlDescription()).Distinct().OrderBy(x => x))
                {
                    yield return new KeyValuePair<Description, string>(Description.Material, item);
                }

                //process the positions
                OntologyRow PositioningOntology = (this.tablePartition.DataSet as XemlDataSet).Ontology.SingleOrDefault(x => x.NameSpaceAlias == "XPO");
                if (PositioningOntology != null)
                {
                    var pos = (this.tablePartition.DataSet as XemlDataSet).Term.Where(t => t.FK_Ontology == PositioningOntology.Id).Join(
                        (this.tablePartition.DataSet as XemlDataSet).Value.Where(x => !x.IsFK_PartitionNull()).Where(x => x.FK_Partition == this.Id && x.FK_Story == this.FK_Story), x => x.Id, y => y.FK_Term, (x, y) => new { term = x.Name, val = y.Value }).Distinct().ToList();

                    foreach (var item in pos)
                    {
                        yield return new KeyValuePair<Description, string>(Description.Position, string.Concat(item.term, ": ", item.val));
                    }
                }
            }

            /// <summary>
            /// returns the TargetTime of the ObservationPoint
            /// </summary>
            internal double GetTargetTime()
            {
                return ObservationRowParent.GetTargetTime();
            }
        }

        partial class SampleRow
        {
            internal void AssignAnnotation(Sample s)
            {
                short? g = (this.tableSample.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, s);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }

            /// <summary>
            /// The intention of this function is that according to Jan Hannemann "Sample die dem gleichen ObservationPoint zugehordnet sind und Biologisch identisch sind." einen gleichen Identifier (integer value) bekommen.
            /// </summary>
            public int GetXemlHashCode()
            {
                int res = 0;
                object sync = new object();
                Parallel.ForEach<PartitionRow, int>(this.GetPartitionRows(), new ParallelOptions(),
                    //localInit
                        delegate()
                        {
                            return 0;
                        },
                    //body
                        delegate(PartitionRow p, ParallelLoopState pls, long index, int state)
                        {
                            return state ^ p.GetXemlHashCode();
                        },
                    //localCleanup
                        delegate(int state)
                        {
                            lock (sync)
                            {
                                res ^= state;
                            }
                        });

                return res;
            }

            internal int GetXemlHashCode(Description d)
            {
                int res = 0;
                object sync = new object();
                Parallel.ForEach<PartitionRow, int>(this.GetPartitionRows(), new ParallelOptions(),
                    //localInit
                        delegate()
                        {
                            return 0;
                        },
                    //body
                        delegate(PartitionRow p, ParallelLoopState pls, long index, int state)
                        {
                            return state ^ p.GetXemlHashCode(d);
                        },
                    //localCleanup
                        delegate(int state)
                        {
                            lock (sync)
                            {
                                res ^= state;
                            }
                        });

                return res;
            }


            internal short GetReplicaGroup()
            {
                return this.GetReplicaGroupMappingRows().Select(x => x.ReplicaGroupRowParent.Id).Distinct().Single();
            }

            internal IEnumerable<ObservationRow> GetObservations()
            {
                return (this.tableSample.DataSet as XemlDataSet).Partition.Where(p => p.FK_Sample == this.Id).Select(p => p.ObservationRowParent);
            }

            #region description
            internal string GetXemlDescription(IEnumerable<Description> describe)
            {
                if (describe == null)
                {
                    throw new ArgumentNullException("describe");
                }
                if (describe.Count() == 0)
                {
                    throw new ArgumentException("must contain at least one element", "describe");
                }

                List<KeyValuePair<Description, string>> descriptionCache = ListDescriptions().Distinct().ToList(); //this is kind of a cache to avoid collecting all description again and again

                List<string> res = new List<string>(); //the description for the current sample row

                foreach (var item in describe.OrderBy(x => x))
                {
                    switch (item)
                    {
                        case Description.TargetTime:
                            res.Add(TimeSpan.FromSeconds(GetTargetTime()).StyleUpXemlFormat());
                            break;
                        case Description.GermPlasm:
                        case Description.DevelopmentalStage:
                        case Description.Material:
                        case Description.Position:
                            res.Add(string.Join(";", descriptionCache.Where(x => x.Key == item).Select(x => x.Value)));
                            break;
                        case Description.StoryId:
                            //group values according to ObservationPoint, Term and Context and select those with row number = 1, compare Scalar-Valued-Function [xeml].[GetEnvironmentalStress]
                            List<short> groupFilter = (this.tableSample.DataSet as XemlDataSet).Value
                                .Zip((this.tableSample.DataSet as XemlDataSet).ObservationPoint, (v, op) => new { val = v, OP = op })
                                .Where(z => z.val.FK_Story == z.OP.FK_Story && z.val.Time <= z.OP.TargetTime)
                                .OrderBy(o => o.val.FK_Term)
                                .ThenBy(o => o.val.Context)
                                .ThenByDescending(o => o.val.Time)
                                .GroupBy(g => new { OP = g.OP.Id, term = g.val.FK_Term, context = g.val.Context })
                                .Select(x => new { x, count = x.Count() })
                                .SelectMany(m => m.x.Select(b => b).Zip(Enumerable.Range(1, m.count), (j, i) => new { m, rownumber = i }))
                                .Where(w => w.rownumber == 1)
                                .SelectMany(f => f.m.x.Select(p => p.val.FK_Term))
                                .OrderBy(b => b)
                                .ToList();

                            //chose only terms which have at least 2 different values
                            List<short> ChangingTerms = (this.tableSample.DataSet as XemlDataSet).Value
                                .Where(x => groupFilter.BinarySearch(x.FK_Term) >= 0)
                                .Select(s => new { term = s.FK_Term, val = s.Value })
                                .GroupBy(g => g.term)
                                .Select(b => new { term = b.Key, c = b.Count() })
                                .Where(w => w.c > 1)
                                .Select(d => d.term)
                                .ToList();

                            List<string> TermValueCombinations = new List<string>();
                            foreach (short TermId in ChangingTerms.OrderBy(x => x))
                            {
                                ValueRow v = (this.tableSample.DataSet as XemlDataSet).Value
                                    .Where(x => x.FK_Term == TermId && x.FK_Story == this.FK_Story && (x.IsTimeNull() || x.Time <= this.GetTargetTime()))
                                    .OrderByDescending(x => x.IsTimeNull() ? 0 : x.Time)
                                    .FirstOrDefault();

                                if (v != null)
                                {
                                    TermValueCombinations.Add(string.Concat((this.tableSample.DataSet as XemlDataSet).Term.First(x => x.Id == TermId).Name, ":", v.Value, v.IsUnitNull() ? string.Empty : v.Unit.Replace("\"", string.Empty)));
                                }
                            }
                            res.Insert(0, string.Join(" ", TermValueCombinations));
                            break;
                        default:
                            throw new ArgumentOutOfRangeException("item", "this switch statement is not preparend to handle the current value");
                    }
                }

                return string.Join(" ", res);
            }

            internal IEnumerable<KeyValuePair<Description, string>> ListDescriptions()
            {
                List<KeyValuePair<Description, string>> descriptions = new List<KeyValuePair<Description, string>>();
                foreach (var item in this.GetPartitionRows().Select(x => x.GetXemlDescription()))
                {
                    descriptions.AddRange(item);
                }
                return descriptions.Distinct();
            }

            /// <summary>
            /// returns the TargetTime of the ObservationPoint
            /// </summary>
            internal double GetTargetTime()
            {
                return this.GetPartitionRows().Select(x => x.GetTargetTime()).Distinct().Single();
            }
            #endregion description
        }

        #region Description
        IEnumerable<Description> test()
        {
            if (GetTargetTimes().Count() > 1)
            {
                yield return Description.TargetTime;
            }

            List<KeyValuePair<Description, string>> descriptions = new List<KeyValuePair<Description, string>>();
            foreach (var item in Sample)
            {
                descriptions.AddRange(item.ListDescriptions());
            }

            foreach (var item in descriptions.Distinct().GroupBy(p => p.Key).Select(g => new { Description = g.Key, DescriptionCount = g.Count() }))
            {
                if (item.DescriptionCount > 1)
                {
                    yield return item.Description;
                }
            }
        }

        internal IEnumerable<double> GetTargetTimes()
        {
            List<double> TargetTimes = new List<double>();
            Parallel.ForEach(Sample, smprow =>
            {
                double TargetTime = smprow.GetTargetTime();
                lock (TargetTimes)
                {
                    int index = TargetTimes.BinarySearch(TargetTime);
                    if (index < 0)
                    {
                        TargetTimes.Insert(~index, TargetTime);
                    }
                }
            });
            return TargetTimes;
        }

        internal enum Description : byte
        {
            StoryId = 0,
            TargetTime = 1,
            GermPlasm = 2,
            DevelopmentalStage = 3,
            Material = 4,
            Position = 5
        }
        #endregion Description

        partial class ObservationRow
        {
            internal void AssignAnnotation(Observation ob)
            {
                short? g = (this.tableObservation.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, ob);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }

            /// <summary>
            /// returns HashCodes from GermPlasm, TargetTime, StoryID, DevelopmentalStage
            /// </summary>
            internal int GetXemlHashCode()
            {
                int res = GetXemlHashCode(Description.GermPlasm) ^ ObservationPointRowParent.GetXemlHashCode();
                foreach (DevelopmentalStageRow d in GetDevelopmentalStageRows())
                {
                    res ^= d.GetXemlHashCode();
                }
                return res;
            }

            internal int GetXemlHashCode(Description d)
            {
                switch (d)
                {
                    case Description.StoryId:
                    case Description.TargetTime:
                        return ObservationPointRowParent.GetXemlHashCode(d);
                    case Description.GermPlasm:
                        return IndividualsPoolRowParent.GetXemlHashCode();
                    case Description.DevelopmentalStage:
                        int res = 0;
                        foreach (DevelopmentalStageRow dr in GetDevelopmentalStageRows())
                        {
                            res ^= dr.GetXemlHashCode();
                        }
                        return res;
                    default:
                        throw new ArgumentOutOfRangeException("d");
                }
            }

            internal IEnumerable<KeyValuePair<Description, string>> GetXemlDescription()
            {
                yield return new KeyValuePair<Description, string>(Description.GermPlasm, IndividualsPoolRowParent.GetXemlDescription());

                foreach (var item in GetDevelopmentalStageRows().Select(x => x.GetXemlDescription()).Distinct().OrderBy(x => x).Select(x => new KeyValuePair<Description, string>(Description.DevelopmentalStage, x)))
                {
                    yield return item;
                }
                yield break;
            }

            /// <summary>
            /// returns the TargetTime of the ObservationPoint
            /// </summary>
            internal double GetTargetTime()
            {
                return ObservationPointRowParent.TargetTime;
            }
        }

        partial class MaterialRow
        {
            internal void AssignAnnotation(Term t)
            {
                short? g = (this.tableMaterial.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, t);
                if (g.HasValue)
                {
                    lock (this.tableMaterial)
                    {
                        this.AnnotationId = g.Value;
                    }
                }
            }

            /// <summary>
            /// returns HashCodes from Material
            /// </summary>
            internal int GetXemlHashCode()
            {
                return TermRowParent.GetXemlHashCode();
            }

            internal string GetXemlDescription()
            {
                return TermRowParent.GetXemlDescription();
            }
        }

        partial class DevelopmentalStageRow
        {
            internal void AssignAnnotation(Term t)
            {
                short? g = (this.tableDevelopmentalStage.DataSet as XemlDataSet).Annotation.Add(this.FK_Document, t);
                if (g.HasValue)
                {
                    this.AnnotationId = g.Value;
                }
            }

            /// <summary>
            /// returns HashCode from DevelopmentalStage
            /// </summary>
            internal int GetXemlHashCode()
            {
                return TermRowParent.GetXemlHashCode();
            }

            internal string GetXemlDescription()
            {
                return TermRowParent.GetXemlDescription();
            }
        }

        partial class ValueRow
        {
            public string GetIsaExportFormatted()
            {
                return string.Concat(this.Value, " ", this.Unit.Replace("\"", string.Empty));
            }
        }

        partial class SampleMappingRow
        {
            public string GetReplicateDescription()
            {
                return string.Format("rg{0}", this.SampleRowParent.GetReplicaGroup());
            }
        }

        public partial class OntologyRow : global::System.Data.DataRow
        {
            // Only here so that PO_* ontologies (PO_Structure and PO_Development would be renamed to PO).
            // Only here so that XEO_* ontologies (XEO_Positioning should be renamed to XPO).
            // Only here for ISAExport.
            internal System.String GetProperNameSpaceAlias()
            {
                if (this.NameSpaceAlias.StartsWith("PO_"))
                {
                    return "PO";
                }
                if (this.NameSpaceAlias == "XEO_Positioning")
                {
                    return "XPO";
                }
                return this.NameSpaceAlias;
            }
        }
        #endregion rows section

        #region Comparer
        public class ReplicaComparer : IComparer<ReplicaGroupRow>, IDisposable
        {
            public ReplicaComparer()
            {
            }

            public int Compare(ReplicaGroupRow x, ReplicaGroupRow y)
            {
                using (SampleComparer c = new SampleComparer())
                {
                    return c.Compare(x.GetReplicaGroupMappingRows().First().SampleRowParent, y.GetReplicaGroupMappingRows().First().SampleRowParent);
                }
            }

            public void Dispose()
            {
            }
        }

        public class SampleComparer : IComparer<SampleRow>, IDisposable
        {
            public SampleComparer()
            {
            }

            public int Compare(SampleRow x, SampleRow y)
            {
                int res;    //this holds the intemediate result of the comparison

                short x_story = x.StoryRowParent.Id;
                short y_story = y.StoryRowParent.Id;
                if ((res = x_story.CompareTo(y_story)) != 0)
                {
                    return res;
                }

                double x_TargetTime = x.GetTargetTime();
                double y_TargetTime = y.GetTargetTime();
                if ((res = x_TargetTime.CompareTo(y_TargetTime)) != 0)
                {
                    return res;
                }

                string x_OPD = x.GetPartitionRows().Select(z => z.ObservationRowParent.GetXemlDescription().Select(d => d.Value).OrderBy(d => d).First()).Distinct().Single();
                string y_OPD = y.GetPartitionRows().Select(z => z.ObservationRowParent.GetXemlDescription().Select(d => d.Value).OrderBy(d => d).First()).Distinct().Single();
                if ((res = x_OPD.CompareTo(y_OPD)) != 0)
                {
                    return res;
                }

                var jahu = x.GetPartitionRows().Select(z => z.GetMaterialRows().Select(w => w.GetXemlDescription()).Distinct().ToList()).Distinct().ToList();

                string x_mat = x.GetPartitionRows().Select(z => z.GetMaterialRows().Select(w => w.GetXemlDescription()).Distinct().OrderBy(u => u).First()).Distinct().Single();
                string y_mat = y.GetPartitionRows().Select(z => z.GetMaterialRows().Select(w => w.GetXemlDescription()).Distinct().OrderBy(u => u).First()).Distinct().Single();
                if ((res = x_mat.CompareTo(y_mat)) != 0)
                {
                    return res;
                }

                //process the possible positions
                OntologyRow PositioningOntology = (x.Table.DataSet as XemlDataSet).Ontology.SingleOrDefault(z => z.NameSpaceAlias == "XPO");

                if (PositioningOntology != null)
                {
                    TermRow firstPositionOntologyTerm = (x.Table.DataSet as XemlDataSet).Term.Where(t => t.FK_Ontology == PositioningOntology.Id).OrderBy(z => z.Id).FirstOrDefault();
                    if (firstPositionOntologyTerm != null)
                    {
                        short x_PartitionId = x.GetPartitionRows().OrderBy(z => z.Id).First().Id;
                        short y_PartitionId = y.GetPartitionRows().OrderBy(z => z.Id).First().Id;

                        var x_value = (x.Table.DataSet as XemlDataSet).Value.Where(z => !z.IsFK_PartitionNull()).Where(z => z.FK_Partition == x_PartitionId && z.FK_Story == x.FK_Story && z.FK_Term == firstPositionOntologyTerm.Id).OrderBy(z => z.Id).First();
                        var y_value = (y.Table.DataSet as XemlDataSet).Value.Where(z => !z.IsFK_PartitionNull()).Where(z => z.FK_Partition == y_PartitionId && z.FK_Story == y.FK_Story && z.FK_Term == firstPositionOntologyTerm.Id).OrderBy(z => z.Id).First();

                        float x_numericvalue;
                        float y_numericvalue;
                        if (float.TryParse(x_value.Value, out x_numericvalue) && float.TryParse(y_value.Value, out y_numericvalue))
                        {
                            return x_numericvalue.CompareTo(y_numericvalue);
                        }

                        return x_value.Value.CompareTo(y_value.Value);
                    }
                }

                return 0;
            }

            public void Dispose()
            {
            }
        }
        #endregion Comparer
    }

}

